package com.clusterswisdom.horn.api.tmall.action.scitem.ThreadOfStore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import com.alibaba.fastjson.JSONObject;
import com.clusterswisdom.horn.api.db.DbUtil;
import com.clusterswisdom.horn.api.tmall.TmallInstance;
import com.taobao.api.domain.InventorySum;
import com.taobao.api.response.InventoryAdjustExternalResponse;
import com.taobao.api.response.InventoryInitialItemResponse;
import com.taobao.api.response.InventoryQueryResponse;
import com.taobao.api.response.ScitemOutercodeGetResponse;

/**
 * 存储ebiz传过来的数据以及中间参数的值
 * @author Jin_tian
 *
 */
public class KeepToDb {
	/**
	 * 存储ebiz传过来的数据
	 * @param ebiz传过来的参数     表名
	 * @throws Exception
	 */
	public static boolean setIntoDb(JSONObject param,String tableName) throws Exception{
		Connection con = DbUtil.getUtil().getConnection();
		//默认为y，因为预警库存调整时，没有isBatchOver字段
		String status = "Y";
		//为了区分预警库存
		if(param.getString("isBatchOver") != null){
			
			status = param.getString("isBatchOver");
		}
		
		String date = param.getString("date");
		
		for (Object object : param.getJSONArray("stockList")) {
			JSONObject item = (JSONObject)object;
			String cityCode = item.getString("cityCode");
			String itemId = item.getString("cai");
			long tmallStock = Long.parseLong(item.getString("tmallStock"));
			
			
			String sql = "insert into "+tableName+"(storeDate , storeId , cai , quantity,data) values(? , ? , ?, ? ,?)";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, date);
			ps.setString(2, cityCode);
			ps.setString(3, itemId);
			ps.setLong(4, tmallStock);
			ps.setDate(5, new java.sql.Date(System.currentTimeMillis()));
			ps.executeUpdate();
			
			ps.close();
		}
		
		DbUtil.getUtil().close();
		return "Y".equals(status);
	}
	/**
	 * 获取商品的id和cai的hashmap
	 * @param 表名
	 * @return HashMap<cai,itemId>
	 */
	public static HashMap<String,String> getDataFromTmall(String tableName) throws Exception{
		Connection con = DbUtil.getUtil().getConnection();
		boolean isNotEnd = true;
		HashMap<String,String> caiAndId = new HashMap<String, String>();
		
		String sqlOfNum = "select count(*) from "+ tableName ;
		PreparedStatement psOfNum = con.prepareStatement(sqlOfNum);
		
		ResultSet rsOfNum = psOfNum.executeQuery();
		int num = rsOfNum.getInt(1);
		for(int i = 0 ;isNotEnd;){
			String sql = "select * from "+ tableName + " where limit ? , 1000";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, i);
			i += 1000;
			
			ResultSet rs = ps.executeQuery();
			isNotEnd =(i < num);
			
			while(rs.next()){
				String cai = rs.getString("cai");
				
				String sqlOfItem = "select * from t_citem where outerId = ?";
				PreparedStatement psOfItem = con.prepareStatement(sqlOfItem);
				ps.setString(1, cai);
				ResultSet rsOfItem = psOfItem.executeQuery();
				String itemId = null;
				//如果存在商家编码对应商品id则取商品id，没有则从天猫中查找
				if(rsOfItem.next()){
					itemId = rsOfItem.getString("itemId");
					
				}else{
					itemId = getAndKeepItem(cai);
					
				}
				caiAndId.put(cai, itemId);
			}
		}
		
		DbUtil.getUtil().close();
		return caiAndId;
		
	}
	
	/**
	 * 从天猫中获取商品商家编码对应商品id,并存入表中
	 * @param cai 商品商家编码
	 * @return 商品商家编码对应的商品id
	 * @throws Exception 
	 */
	public static String getAndKeepItem(String cai) throws Exception{
		Connection con = DbUtil.getUtil().getConnection();
		
		JSONObject param = new JSONObject();
		param.put("outer_code", cai);
		ScitemOutercodeGetResponse response = (ScitemOutercodeGetResponse)TmallInstance.getInstance().executeRequest(param, "scitem", "scitemOutercodeGet");
		String itemId =  Long.toString(response.getScItem().getItemId());
		String sql = "insert into t_citem(itemId , outerId) values( ? ,?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, itemId);
		ps.setString(2, cai);
		ps.execute();
		
		ps.close();
		
		DbUtil.getUtil().close();
		return itemId;
	}
	
	/**
	 * 根据商品id和商品商家编码获取商品当前的区域库存
	 * @param caiAndId
	 * @throws Exception
	 */
	public static void  updateItemByItemId(HashMap<String,String> caiAndId ,String tableName) throws Exception{
		Collection<String> itemIds = caiAndId.values();
		Collection<String> cais = caiAndId.keySet();
		Connection con = DbUtil.getUtil().getConnection();
		//循环将表中的商品id补到对应的商品商家编码上
		for(String cai:cais){
			String itemId = caiAndId.get(cai);
			
			String sql = "update " + tableName + " set( ci = ? ) where cai = ?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, itemId);
			ps.setString(2, cai);
			
			ps.execute();
			
			ps.close();
		}
			
		StringBuffer sc_item_ids =new StringBuffer();
		JSONObject param = new JSONObject();
		int i = 1;
		for(String itemId : itemIds){
			sc_item_ids.append(itemId);
			if(i < 50){
				sc_item_ids.append("^");
				
			}else{
				param.put("sc_item_ids", sc_item_ids);
				InventoryQueryResponse response = (InventoryQueryResponse)TmallInstance.getInstance().executeRequest(param, "scitem", "inventoryquery");
				List<InventorySum> inventorysums = response.getItemInventorys();
				for(InventorySum inventorySum : inventorysums){
					String sc_item_code = inventorySum.getScItemCode();
					
					String store_code = inventorySum.getStoreCode();
					Long quantity = inventorySum.getQuantity();
					
					String sql ="update " + tableName + " set(currentNum = ? ,state = 2) where cai = ? and storeId = ?" ;
					PreparedStatement ps = con.prepareStatement(sql);
					ps.setLong(1, quantity);
					ps.setString(2, sc_item_code);
					ps.setString(3, store_code);
					ps.execute();
					
					ps.close();
				}
			}
			
		}
		
		DbUtil.getUtil().close();
	}
	
	/**
	 * 将现库存和将要同步到天猫的库存同步到天猫
	 * @param tableName
	 * @throws Exception
	 */
	public void KeepToTmall(String tableName) throws Exception{
		Connection con = DbUtil.getUtil().getConnection();
		boolean isNotEnd = true;
		for(int i = 0 ;isNotEnd;){
			String sql = "select * from "+ tableName + " where state = 2 limit ? , 1000 ";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, i);
			i += 1000;
			ResultSet rs = ps.executeQuery();
			isNotEnd = rs.getRow() <1000? false : true;
			
			while(rs.next()){
				String cai = rs.getString("cai");
				String storeId = rs.getString("storeId");
				long quantity = rs.getLong("quantity");
				long currentNum = rs.getLong("currentNum");
				
				JSONObject items = new JSONObject();
				items.put("scItemId", "0");
				items.put("scItemCode", cai);
				items.put("inventoryType", "1");
				items.put("direction", currentNum - quantity <0 ? "-1" : "1");
				items.put("quantity", Long.toString(Math.abs(currentNum - quantity)));
				
				String item = items.toJSONString();
				
				JSONObject param = new JSONObject();
				param.put("biz_unique_code", Long.toString(System.currentTimeMillis()));
				param.put("operate_type", "BALANCE");
				param.put("biz_type", "BALANCE");
				param.put("operate_time", new Date());
				param.put("store_code", storeId);
				param.put("items",item);
				
				
				InventoryAdjustExternalResponse response = (InventoryAdjustExternalResponse)TmallInstance.getInstance().executeRequest(param,"scitem","inventoryadjustexternal");
				
			}
		}
		
		DbUtil.getUtil().close();
		
	}
	
	/**
	 * 将只有将要同步到天猫没有现有库存的即要初始化的区域库存
	 * @throws Exception 
	 */
	
	public void initialToTmall(String tableName) throws Exception{
		Connection con = DbUtil.getUtil().getConnection();
		boolean isNotEnd = true;
		for(int i = 0 ;isNotEnd;){
			String sql = "select * from "+ tableName + " where  state = null limit ? ,1000";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, i);
			i += 1000;
			ResultSet rs = ps.executeQuery();
			isNotEnd = rs.getRow() <1000? false : true;
			
			while(rs.next()){
				String cai = rs.getString("cai");
				String storeId = rs.getString("storeId");
				long quantity = rs.getLong("quantity");
				
				JSONObject items = new JSONObject();
				items.put("scItemId", "0");
				items.put("scItemCode", cai);
				items.put("inventoryType", "1");
				items.put("quantity", quantity);
				
				String item = items.toJSONString();
				
				JSONObject param = new JSONObject();
				param.put("store_code", storeId);
				param.put("items",item);
				
				InventoryInitialItemResponse response = (InventoryInitialItemResponse)TmallInstance.getInstance().executeRequest(param, "scitem", "inventoryinitialitem");
				
			}
		}
		
		DbUtil.getUtil().close();
	}
	
//	//对单条的数据进行设置，默认为该商品对应的区域调整已调整过了
//	public static void keepAndSetItem(JSONObject item,String tableName) throws Exception {
//		// TODO Auto-generated method stub
//		String cityCode = item.getString("cityCode");
//		
//		String cai = item.getString("cai");
//		int tmallStock = Integer.parseInt(item.getString("tmallStock"));
//		
//		Connection con = DbUtil.getUtil().getConnection();
//		String sql = "update "+ tableName+" set( quantity= ?) where cai = ? and storeId = ?" ;
//		
//		PreparedStatement ps = con.prepareStatement(sql);
//		ps.setInt(1, tmallStock);
//		ps.setString(2, cai);
//		ps.setString(3, cityCode);
//		
//		ps.executeUpdate();
//		//更新数据库中现在库存量
//		updateCurrentNum(cityCode,cai,tableName);
//		//库存同步到天猫
//		excute(cityCode,cai,tableName);
//		
//		ps.close();
//		DbUtil.getUtil().close();
//	}
//	
//	/**
//	 * 单条库存数据库中先库存数据更新
//	 * @param cityCode
//	 * @param cai
//	 * @param tableName
//	 * @throws Exception
//	 */
//	public static void updateCurrentNum (String cityCode,String cai,String tableName)throws Exception{
//		Connection con = DbUtil.getUtil().getConnection();
//		String sql = "select * from " + tableName + " where cai = ? and storeId = ?  ";
//		PreparedStatement ps = con.prepareStatement(sql);
//		ps.setString(1, cai);
//		ps.setString(2, cityCode);
//		ResultSet rs = ps.executeQuery();
//		
//		String itemId = rs.getString("ci");
//		JSONObject param = new JSONObject();
//		param.put("sc_item_ids", itemId);
//		param.put("store_codes", cityCode);
//		
//		InventoryQueryResponse response = (InventoryQueryResponse)TmallInstance.getInstance().executeRequest(param, "scitem", "inventoryquery");
//		List<InventorySum> items=response.getItemInventorys();
//		long quantity = 0;
//		for(InventorySum item : items){
//			quantity = item.getQuantity();
//		}
//		String updateSql = "update "+tableName+ " set ( currentNum = ?) where cai = ? and storeId = ? ";
//		ps = con.prepareStatement(updateSql);
//		ps.setLong(1, quantity);
//		ps.setString(2, cai);
//		ps.setString(3, cityCode);
//		ps.executeUpdate();
//		
//		ps.close();
//		
//		DbUtil.getUtil().close();
//	}
//	
//	/**
//	 * 单条库存同步到天猫
//	 * @param cityCode
//	 * @param cai
//	 * @param tableName
//	 * @throws Exception
//	 */
//	public static void excute(String cityCode,String cai,String tableName)throws Exception{
//		Connection con = DbUtil.getUtil().getConnection();
//		String sql = "select * form " + tableName + " where where cai = ? and storeId = ?  "; 
//		PreparedStatement ps = con.prepareStatement(sql);
//		ps.setString(1, cai);
//		ps.setString(2, cityCode);
//		ResultSet rs = ps.executeQuery();
//		
//		long quantity = rs.getLong("quantity");
//		long currentNum = rs.getLong("currentNum");
//		
//		JSONObject items = new JSONObject();
//		items.put("scItemId", "0");
//		items.put("scItemCode", cai);
//		items.put("inventoryType", "1");
//		items.put("direction", currentNum - quantity <0 ? "-1" : "1");
//		items.put("quantity", Long.toString(Math.abs(currentNum - quantity)));
//		
//		String item = items.toJSONString();
//		
//		JSONObject param = new JSONObject();
//		param.put("biz_unique_code", Long.toString(System.currentTimeMillis()));
//		param.put("operate_type", "BALANCE");
//		param.put("biz_type", "BALANCE");
//		param.put("operate_time", new Date());
//		param.put("store_code", cityCode);
//		param.put("items",item);
//		
//		
//		InventoryAdjustExternalResponse response = (InventoryAdjustExternalResponse)TmallInstance.getInstance().executeRequest(param,"scitem","inventoryadjustexternal");
//		
//		DbUtil.getUtil().close();
//	}
}
